package com.wframe.project.former.dataservices.marketing;

import com.frame.database.JBaseDao;
import com.frame.database.JdataBean;
import org.apache.commons.lang3.StringUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2017/6/6.
 */
public class PromotionsDao extends JBaseDao {

    public PromotionsDao() {
        super("wb_promotions");
    }

    public String addPromotions(JdataBean bean) throws Exception {
        return getSession().saveReturnKey("wb_promotions", bean.getDataMap());
    }

    public void updateStop(int id, int state) throws Exception {
        getSession().update("update wb_promotions set state=? where id=?", new Object[]{state, id});
    }

    public JdataBean saveSession(int id) throws Exception {
        String sql = "SELECT id,`name` pname,`code`,types,(select (ifnull(pr.amount,0)-ifnull(pr.djamount,0)) from  wb_products_prices pr where pr.id=n.product_val) productamount from(SELECT *from wb_products "
                + "WHERE id in (SELECT DISTINCT(product_val) from wb_promotions WHERE product_type=1 and promotion_type=0))m "
                + "join (SELECT GROUP_CONCAT(case promotion_type when '0' THEN '单品促销' when '1' THEN "
                + "'买送促销' when '2' THEN '满减促销' when '3' THEN '满赠促销' end) types,product_val "
                + "from (SELECT * from wb_promotions WHERE product_type=1 and product_val "
                + "in (SELECT DISTINCT(product_val) from wb_promotions WHERE product_type=1 and promotion_type=0))t "
                + "GROUP BY product_val)n on m.id=n.product_val and m.id=?";
        return getSession().getObject(sql, new Object[]{id});
    }

    public List<JdataBean> controlsalesPro(int promotionid) throws Exception {
        return getSession().query("select * from wb_promotion_controlsale WHERE promotion_id=?", new Object[]{promotionid});
    }

    public JdataBean productSingle(String proid) throws Exception {
        return getSession().getObject("select * from wb_promotions where  now() < end_time and (state=1 or state=0) and promotion_type=0 and product_val=?", new Object[]{proid});
    }

    public List<JdataBean> productSelect() throws Exception {
        return getSession().query("SELECT u.flag from wb_products t LEFT JOIN (SELECT product_val,1 flag from wb_promotions WHERE promotion_type=0 and platform in(0,1) and  end_time > NOW() and product_type=1 and product_val in(SELECT id from wb_products)) u on t.id=u.product_val", null);
    }

    public String getProducrPrice(String productid) throws Exception {
        JdataBean jdataBean = getSession().getObject("SELECT * from wb_products_pricegrade where productid=? and grade=11", new Object[]{productid});
        if (jdataBean == null) {
            return null;
        }
        String price = jdataBean.getString("price");
        return price;
    }

    public String getProductType(String productid) throws Exception {
        JdataBean jdataBean = getSession().getObject("select id,product_type producttype from wb_promotions where promotion_type=0 and product_val=? and end_time > NOW()", new Object[]{productid});
        if (jdataBean == null) {
            return null;
        }
        String products_types = jdataBean.getString("producttype");
        return products_types;
    }


    public String getProductMoneyOffTypeById(String productid) throws Exception {

        JdataBean class_id = getSession().getObject("select id,pid from wb_products_class where code =(select classcode from wb_products_and_class where productid=?)",new Object[]{productid});
        String classid="";
        String classpid="";
        if (class_id != null) {
            classid = class_id.getString("id");
            classpid  = class_id.getString("pid");
        }


        JdataBean jdataBean = getSession().getObject("SELECT GROUP_CONCAT(case promotion_type when '0' THEN '单品促销'  when '1' THEN '买送促销' when '2' THEN '满减促销' when '3' THEN '满赠促销' end) moneytypes from wb_promotions WHERE end_time>NOW() and (state=1 or state=0) and ( product_type=0 or (product_type=1 and product_val =?) or (product_type=2 and product_val in (SELECT promotionid from wb_promotion_product WHERE productid=?)) or (product_type=3 and product_val in (?,?,?)))", new Object[]{productid, productid,1,classid,classpid});
        if (jdataBean == null) {
            return null;
        }
        String moneyoffType = jdataBean.getString("moneytypes");
        return moneyoffType;
    }

    /**
     * 查询多个商品所参加的促销活动和第一预售价
     * @param ids 商品ID数组
     * @return
     * @throws Exception
     */
    public List<JdataBean> getProductPromotionTypeByIds(String[] ids) throws Exception  {
        List<JdataBean> result = new ArrayList<>(0);
        if(ids != null && ids.length > 0) {
            String idsStr = StringUtils.join(ids,",");
            String sql = "SELECT " +
                    "id, " +
                    "GROUP_CONCAT(pppp.pm_type) AS pms, " +
                    "pppp.ppr_price as price " +
                    "FROM " +
                    "( " +
                    "SELECT " +
                    "pp.id, pm.id AS pm_id, " +
                    "ppr.price as ppr_price, " +
                    "( " +
                    "CASE promotion_type " +
                    "WHEN '0' THEN " +
                    "'单品促销' " +
                    "WHEN '1' THEN " +
                    "'买送促销' " +
                    "WHEN '2' THEN " +
                    "'满减促销' " +
                    "WHEN '3' THEN " +
                    "'满赠促销' " +
                    "END " +
                    ") pm_type " +
                    "FROM " +
                    "( " +
                    "SELECT " +
                    "* " +
                    "FROM " +
                    "wb_products " +
                    "WHERE " +
                    "ID IN (" + idsStr +
                    ") " +
                    ") pp " +
                    "LEFT JOIN wb_promotion_product pmp ON pmp.productid = pp.id " +
                    "LEFT JOIN wb_products_and_class ppac ON pp.id = ppac.productid " +
                    "LEFT JOIN wb_products_pricegrade ppr ON pp.id = ppr.productid and ppr.grade = 11 " +
                    "LEFT JOIN ( " +
                    "SELECT " +
                    "pc.*, pac.id AS pac_id, " +
                    "pac.productid, " +
                    "pac.classid, " +
                    "pac.classcode " +
                    "FROM " +
                    "wb_products_class pc " +
                    "RIGHT JOIN ( " +
                    "SELECT " +
                    "* " +
                    "FROM " +
                    "wb_products_and_class " +
                    "WHERE " +
                    "productid IN ( " + idsStr +
                    ") " +
                    ") pac ON ( " +
                    "pc.id = pac.classid " +
                    "OR pac.classcode LIKE CONCAT(pc.`code`, '.%') " +
                    ") " +
                    ") ptc ON ptc.pac_id = ppac.id " +
                    "LEFT JOIN ( " +
                    "SELECT " +
                    "prom.* " +
                    "FROM " +
                    "wb_promotions prom " +
                    "WHERE " +
                    "(prom.state = 1 or prom.state = 0) " +
                    "AND prom.start_time <= NOW() " +
                    "AND prom.end_time > NOW() " +
                    ") pm ON ( " +
                    "pm.product_type = 0 " +
                    "AND pm.promotion_type != 0 " +
                    "AND pm.promotion_type != 1 " +
                    ") " +
                    "OR ( " +
                    "pm.product_type = 1 " +
                    "AND pm.product_val = pp.id " +
                    ") " +
                    "OR ( " +
                    "pm.product_type = 2 " +
                    "AND pm.id = pmp.promotionid " +
                    ") " +
                    "OR ( " +
                    "pm.product_type = 3 " +
                    "AND pm.product_val = ptc.id " +
                    ") " +
                    "GROUP BY " +
                    "pp.id," +
                    "pm.promotion_type " +
                    "ORDER BY " +
                    "pp.id  " +
                    ") pppp " +
                    "GROUP BY " +
                    "pppp.id";
            return getSession().query(sql,null);
        }
        return result;
    }

    public JdataBean getSingleProduct(String productid) throws Exception {
        return getSession().getObject("SELECT m.id,m.`name` pname,m.`code`,(select ifnull(pr.amount,0)-ifnull(pr.djamount,0) from  wb_products_prices pr where pr.productid=m.id) productamount from  wb_products m where m.`status`=2 and m.id=?", new Object[]{productid});
    }

    public List<JdataBean> queryProductPromotionid(String promotionid) throws Exception {
        return getSession().query("select * from wb_promotion_product where promotionid=?", new Object[]{promotionid});
    }


}
